﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Windows;
using ExcelLoader;
using MDSCommon;

namespace MDSAdministration
{
    public class ExcelUpload
    {
        private ExcelHelper excelHelper = new ExcelHelper();
        public List<SheetDetailLoad> Sheets { get; set; }
        public string OpenExcelFile()
        {
            string filename = excelHelper.OpenExcelFile();
            using (new WaitCursor())
            {
                GetExcelSheetNames();
            }
            return filename;
        }
        public void GetExcelSheetNames()
        {
            var items = excelHelper.GetExcelSheetNames();

            Sheets = new List<SheetDetailLoad>();

            foreach (var i in items)
            {
                var sdl = new SheetDetailLoad(i.SheetName) { Count = i.Count };
                Sheets.Add(sdl);
            }

        }

        public string FileName
        {
            get { return excelHelper.FileName; }
            set { excelHelper.FileName = value; }
        }
        public void deployExcelSheet(SheetDetailLoad Sheet, DeploymentBuilder db, CompareModel cModel, ServerMDSDetails Server)
        {
           
            try
            {
                getExcelMembers(db, Sheet, cModel, Server);

                var dlg = new DeploymentWindow();
                dlg.db = db;
                dlg.ShowDialog();
            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
            }
        }


        private void getExcelMembers(DeploymentBuilder db, SheetDetailLoad Sheet, CompareModel cModel, ServerMDSDetails Server)
        {
            CompareEntity entity = cModel.Entities[CompareEntity.getEntityKey(Sheet.Name)];

            List<EntityColumn> lSelectedColumns = new List<EntityColumn>();
            foreach (DataColumn col in Sheet.Data.Columns)
            {
                if (entity.Attributes.ContainsKey(CompareAttribute.getAttributeKey(col.ColumnName, Constants.MEMBER_TYPE_LEAF)))
                {
                    lSelectedColumns.Add(EntityColumn.ConvertDataColumn(entity.Attributes[CompareAttribute.getAttributeKey(col.ColumnName, Constants.MEMBER_TYPE_LEAF)]));
                }
                else if (col.ColumnName == Constants.ATTRIBUTE_NEWCODE)
                {
                    lSelectedColumns.Add(new EntityColumn() { Name = col.ColumnName, isDomainEntity = false, dataType = MDSDataTypes.String, isStagingAttribute = true });
                }
            }

            DataSet mdsData = getMDSDataFromDatabase(entity, lSelectedColumns, Sheet, Server);

            var aem = new ActionEntityMember();
            aem.EntityStagingTableName = entity.StagingBase;
            DataRowView drvMDSData = null;
            string codeValue;
            foreach (DataRowView drv in Sheet.Data.DefaultView)
            {
                var am = new ActionMember();
                drvMDSData = null;
                codeValue = drv[Constants.MDSADMINISTRATION_CODE].ToString();

                foreach (DataRowView drvMDS in mdsData.Tables[0].DefaultView)
                {
                    if (drvMDS[Constants.MDSADMINISTRATION_CODE].ToString() == codeValue)
                    {
                        drvMDSData = drvMDS;
                        break;
                    }
                }

                foreach (var selectedColumn in lSelectedColumns)
                {
                    var m = new MemberValue()
                    {
                        MemberName = selectedColumn.Name,
                        ValueSource = BusinessHelper.SetMemberValue(selectedColumn, drv),
                        dataType = selectedColumn.dataType,
                        Attribute =entity.Attributes[CompareAttribute.getAttributeKey(Constants.MDSADMINISTRATION_NAME,Constants.MEMBER_TYPE_LEAF)]
                    };
                    //handle nulling records correctly
                    if (drvMDSData == null)
                    {
                        //if this is a new member use the same member as source
                        m.ValueDestination = BusinessHelper.SetMemberValue(selectedColumn, drv);
                    }
                    else
                    {
                        m.ValueDestination = BusinessHelper.SetMemberValue(selectedColumn, drvMDSData);
                    }
                    am.lMemberValue.Add(m);
                }

                aem.LActionMembers.Add(am);
            }
            db.LActionEntityMembers.Add(aem);
        }

        private DataSet getMDSDataFromDatabase(CompareEntity entity, List<EntityColumn> lSelectedColumns, SheetDetailLoad Sheet, ServerMDSDetails Server)
        {
            string sqlQueryColumns = "";
            string sqlInCodeValues = "";
            bool addSemiCol = false;
            foreach (var selectedColumn in lSelectedColumns)
            {
                if (selectedColumn.isStagingAttribute == false)
                {
                    if (addSemiCol)
                    {
                        sqlQueryColumns += ", ";
                    }

                    if (selectedColumn.isDomainEntity)
                    {
                        sqlQueryColumns += selectedColumn.Name + "_Code AS [" + selectedColumn.Name + "]";
                    }
                    else
                    {
                        sqlQueryColumns += "[" + selectedColumn.Name + "]";
                    }

                    addSemiCol = true;
                }
            }

            addSemiCol = false;
            foreach (DataRow dr in Sheet.Data.Rows)
            {
                if (addSemiCol)
                {
                    sqlInCodeValues += ", ";
                }

                sqlInCodeValues += "'" + dr[Constants.MDSADMINISTRATION_CODE].ToString() + "'";

                addSemiCol = true;
            }

            return DataHelper.GetData("SELECT " + sqlQueryColumns + " FROM mdm." + entity.Model.Name + "_" + entity.Name + " WHERE " + Constants.MDSADMINISTRATION_CODE + " IN (" + sqlInCodeValues + ")", Server);
        }

        public bool ExcelSheetCheck(bool? CheckCodel, bool? CheckName, CompareModel cModel, SheetDetailLoad Sheet)
        {
            if (!ExcelCheckEntityExist(Sheet.Name, cModel))
            {
                Sheet.IsSheetNameInvalid = true;
                return false;
            }
            if (CheckCodel == false && !ExcelCheckCodeExist(Sheet.Data))
            {
                Sheet.IsCodeMissing = true;
                return false;
            }
            if (CheckName == false && !ExcelCheckNameExist(Sheet.Data))
            {
                Sheet.IsNameMissing = true;
                return false;
            }

            //Check attribute isSystem
            if (ExcelCheckAttributeisSystem(Sheet, cModel))
            {
                return false;
            }

            return true;
        }

        public bool ExcelSheetCheckCodeGen(CompareModel cModel, SheetDetailLoad Sheet)
        {
            //Check code value Auto Gen
            if (cModel.Entities[CompareEntity.getEntityKey(Sheet.Name)].CodeGenSeed == 0)
            {
                int count = (from r in Sheet.Data.AsEnumerable()
                             where String.IsNullOrEmpty(DataHelper.dataCellStringConv(r, Constants.MDSADMINISTRATION_CODE))
                             select r).Count();
                if (count > 0)
                {
                    Sheet.IsCodeNotAutoGenerate = true;
                    return false;
                }
            }
            return true;
        }

        public void ExcelSheetAnalysis(SheetDetailLoad Sheet, bool? CheckCodel, bool? CheckName, CompareModel cModel, ServerMDSDetails Server)
        {
            if (ExcelSheetCheck(CheckCodel, CheckName, cModel, Sheet) == false)
            {
                return;
            }

            if (ExcelSheetCheckCodeGen(cModel, Sheet) == false)
            {
                return;
            }

            DataView dv = Sheet.Data.DefaultView;

            //Check code value for duplicates
            if (cModel.Entities[CompareEntity.getEntityKey(Sheet.Name)].CodeGenSeed == 0)
            {
                var rDuplicateCodeValues = from r in dv.Table.AsEnumerable()
                                           where String.IsNullOrEmpty(r.Field<string>(Constants.MDSADMINISTRATION_CODE)) == false
                                           group r by r[Constants.MDSADMINISTRATION_CODE] into g
                                           where g.Count() > 1
                                           select new { Value = g.Key };

                //Check to see if there are any records.
                if (rDuplicateCodeValues.Any())
                {
                    dv[0][Constants.MDSADMINISTRATION_CODE].ToString();

                    foreach (var dupRec in rDuplicateCodeValues)
                    {
                        string duplicateCodeValue = dupRec.Value.ToString();

                        var rowsToUpdate =
                            dv.Table.AsEnumerable().Where(r => r.Field<string>(Constants.MDSADMINISTRATION_CODE) == duplicateCodeValue);

                        foreach (var row in rowsToUpdate)
                        {
                            row.SetField(Constants.EXECELUPLOAD_CODE_DUP, true);
                        }
                    }
                    Sheet.IsCodeDuplicate = true;
                    
                    return;
                }
            }

            //Loop through columns
            foreach (DataColumn c in dv.Table.Columns)
            {
                string columnName = c.ColumnName;

                if (columnName.Contains(Constants.EXCELUPLOAD_MDSADMIN))
                {
                    continue;
                }

                //Check missing and inactive domaing code values
                if (ExcelCheckAttributeExist(Sheet.Name, cModel, columnName))
                {
                    if (cModel.Entities[CompareEntity.getEntityKey(Sheet.Name)].Attributes[(CompareAttribute.getAttributeKey(columnName, Constants.MEMBER_TYPE_LEAF))].isDomainAttribute)
                    {
                        string DomainEntityName = cModel.Entities[CompareEntity.getEntityKey(Sheet.Name)].Attributes[(CompareAttribute.getAttributeKey(columnName, Constants.MEMBER_TYPE_LEAF))].DomainEntityName;
    
                        DataTable dtExcelDistinct = dv.ToTable(true, columnName);

                        var query = dtExcelDistinct.AsEnumerable().Where(r => r.Field<string>(columnName) == null);

                        foreach (var row in query.ToList())
                        {
                            row.Delete();
                        }


                        if (dtExcelDistinct.Rows.Count > 0)
                        {
                            string sqlQuery = "SELECT Code AS '" + columnName + "'," + Constants.MEMEBER_STATUS_ID +
                                              " FROM mdm." +
                                              cModel.Entities[CompareEntity.getEntityKey(DomainEntityName)].EntityTable;

                            DataSet ds = DataHelper.GetData(sqlQuery, Server);

                            var resultDomainInvalid = from ex in dtExcelDistinct.AsEnumerable()
                                join mds in ds.Tables[0].AsEnumerable()
                                    on ex[columnName] equals mds[columnName] into j
                                from x in j.DefaultIfEmpty()
                                where
                                    (x == null || x.Field<byte>(Constants.MEMEBER_STATUS_ID) == 2) &&
                                    (ex[columnName] != null || ex[columnName] != System.DBNull.Value)
                                select new
                                {
                                    Code = ex[columnName].ToString(),
                                    IsInActive = x != null
                                };

                            foreach (var domainInvalidRec in resultDomainInvalid)
                            {
                                if (domainInvalidRec.IsInActive == true)
                                {
                                    var rowsToUpdate =
                                        dv.Table.AsEnumerable()
                                            .Where(r => r.Field<string>(columnName) == domainInvalidRec.Code);
                                    foreach (var row in rowsToUpdate)
                                    {
                                        row.SetField(Constants.EXCELUPLOAD_MDSADMIN + columnName + "_InActive", true);
                                    }
                                    Sheet.IsDomainInactive = true;
                                }
                                else
                                {
                                    var rowsToUpdate =
                                        dv.Table.AsEnumerable()
                                            .Where(r => r.Field<string>(columnName) == domainInvalidRec.Code);
                                    foreach (var row in rowsToUpdate)
                                    {
                                        row.SetField(Constants.EXCELUPLOAD_MDSADMIN + columnName + "_Missing", true);
                                    }
                                    Sheet.IsDomainMissing = true;
                                }

                            }
                        }
                    }
                }
                else if (columnName == Constants.ATTRIBUTE_NEWCODE)
                {
                    //TODO change code value
                }
                else
                {
                    Sheet.IsColumnInvalidName = true;
                    Sheet.LColumnInvalidName.Add(columnName);
                    
                }
            }
        }

        private bool ExcelCheckAttributeisSystem(SheetDetailLoad Sheet, CompareModel cModel)
        {
            foreach (DataColumn c in Sheet.Data.Columns)
            {
                string columnName = c.Caption;
                if (
                   cModel.Entities[CompareEntity.getEntityKey(Sheet.Name)].Attributes.ContainsKey(
                        CompareAttribute.getAttributeKey(columnName, 1)))
                {
                    var a = cModel.Entities[CompareEntity.getEntityKey(Sheet.Name)].Attributes[
                        CompareAttribute.getAttributeKey(columnName, 1)];
                    if (columnName.ToUpper() == a.Name.ToUpper() && a.IsSystem == true && a.IsCode == false && a.IsName == false)
                    {
                        Sheet.IsHasSystemAttribute = true;
                        Sheet.LHasSystemAttributes.Add(columnName);
                    }
                }
            }
            
            return Sheet.IsHasSystemAttribute;
        }

        public DataView LoadSpreadSheet(SheetDetailLoad sd, bool? Trim, string FileName, CompareModel cModel)
        {
            try
            {
                excelHelper.FileName = FileName;
                excelHelper.SetConnectionString();
                DataTable dt = excelHelper.ReadExcelSheet(sd.SheetName, Trim == true);
                if (dt != null)
                {
                    bool hasCode = false;

                    List<string> ListDomainEntityNames = new List<string>();

                    foreach (DataColumn col in dt.Columns)
                    {
                        if (col.DataType.ToString() != Constants.SYSTEM_STRING)
                        {
                            sd.LColumnInvalidFormat.Add(col.ColumnName);
                            sd.IsColumnInvalidFormat = true;
                            return null;
                        }
                    }
                    
                    //Can not add to collection while looping
                    foreach (DataColumn col in dt.Columns)
                    {
                        //sheets should have a code column. Check just in case.
                        if (col.ColumnName == Constants.MDSADMINISTRATION_CODE)
                        {
                            hasCode = true;
                        }

                        if (cModel.Entities.ContainsKey(CompareEntity.getEntityKey(sd.Name)))
                        {
                            if (cModel.Entities[CompareEntity.getEntityKey(sd.Name)].Attributes.ContainsKey(CompareAttribute.getAttributeKey(col.ColumnName, Constants.MEMBER_TYPE_LEAF)))
                            {
                                if (cModel.Entities[CompareEntity.getEntityKey(sd.Name)].Attributes[CompareAttribute.getAttributeKey(col.ColumnName, Constants.MEMBER_TYPE_LEAF)].isDomainAttribute)
                                {
                                    ListDomainEntityNames.Add(Constants.EXCELUPLOAD_MDSADMIN + col.ColumnName + "_Missing");
                                    ListDomainEntityNames.Add(Constants.EXCELUPLOAD_MDSADMIN + col.ColumnName + "_InActive");
                                }
                            }
                        }
                    }

                    if (hasCode)
                    {
                        dt.Columns.Add(Constants.EXECELUPLOAD_CODE_DUP, typeof(bool));
                        dt.Columns.Add(Constants.EXECELUPLOAD_CODE_MATCH, typeof(bool));
                        dt.Columns.Add(Constants.EXECELUPLOAD_NAME_MATCH, typeof(bool));
                        

                        foreach (DataRow row in dt.AsEnumerable())
                        {
                            row.SetField(Constants.EXECELUPLOAD_CODE_DUP, false);
                            row.SetField(Constants.EXECELUPLOAD_CODE_MATCH, false);
                            row.SetField(Constants.EXECELUPLOAD_NAME_MATCH, false);
                        }
                    }
                    foreach (string colName in ListDomainEntityNames)
                    {
                        dt.Columns.Add(colName, typeof(bool));

                        foreach (DataRow row in dt.AsEnumerable())
                        {
                            row.SetField(colName, false);
                        }
                    }
                    dt.TableName = sd.Name;
                    sd.Data = dt;
                    return dt.DefaultView;
                }
            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
            }
            return null;
        }
        private bool ExcelCheckNameExist(DataTable dt)
        {
            foreach (DataColumn c in dt.Columns)
            {
                string columnName = c.Caption;
                if (columnName == Constants.MDSADMINISTRATION_NAME)
                {
                    return true;
                }
            }
            return false;
        }
        private bool ExcelCheckCodeExist(DataTable dt)
        {
            if (dt != null)
            {
                foreach (DataColumn c in dt.Columns)
                {
                    string columnName = c.Caption;
                    if (columnName == Constants.MDSADMINISTRATION_CODE)
                    {
                        return true;
                    }
                }
            }
            return false;
        }
        private bool ExcelCheckEntityExist(string entityName, CompareModel cModel)
        {
            return cModel.Entities.ContainsKey(CompareEntity.getEntityKey(entityName));
        }
        private bool ExcelCheckAttributeExist(string EntityName, CompareModel cModel, string AttributeName)
        {
            return
                cModel.Entities[CompareEntity.getEntityKey(EntityName)].Attributes.ContainsKey(
                    CompareAttribute.getAttributeKey(AttributeName, Constants.MEMBER_TYPE_LEAF));
        }

    }
}
